package com.atzon.DAO;

import com.atzon.constant.Const;
import com.atzon.model.Dept;
import com.atzon.model.User;
import com.atzon.model.UserFocus;
import com.atzon.model.UserInfo;
import com.atzon.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class UserDAO extends BaseDAO {
    public void delFocusById(Integer id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            qr.update("delete from user_focus where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void setPass(String pass, String email) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            qr.update("update user set password =? where email =?", pass, email);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updatePic(Integer userId, String path) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            qr.update("update user_info set pic = ? where user_id = ?",
                    path, userId);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void reLoginTime(Date rtime, Integer user_id) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            qr.update("update user_info set login_time =? where user_id =?", rtime, user_id);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updateUserInfo(Integer uid, Integer look) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            qr.update("update user_info set look =? where user_id =?", look, uid);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void addUserFocus(Integer userId, Integer focusid) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            qr.update("insert into user_focus value(?,?,?)", null, userId, focusid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void modMyInfo(Integer userId, String realName, String age, String phone, String sex, String secret, String dept) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            qr.update("update user_info set real_name = ?, age = ?, phone = ?, gender = ? where user_id =?",
                    realName, age, phone, sex, userId);
            Dept newDept = findDeptByName(dept);
            qr.update("update user set is_secret = ?, dept_id = ? where id =?", secret, newDept.getId(), userId);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public long getTotalUser(String search) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            String sql = "select count(*) as total from user_info ui left join user u on ui.user_id = u.id";
            if (null == search) {
                Map<String, Object> map = qr.query(sql, new MapHandler());
                total = Long.parseLong(map.get("total").toString());
            } else {
                sql = sql + " where ui.real_name like ? or u.username like ? ";
                search = search + "%";
                Map<String, Object> map = qr.query(sql, new MapHandler(), search, search);
                total = Long.parseLong(map.get("total").toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    public long getTotalFocus(Integer userId) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            String sql = "select count(*) as total from user_focus where user_id = ?";
            Map<String, Object> map = qr.query(sql, new MapHandler(), userId);
            total = Long.parseLong(map.get("total").toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    public User findById(String id) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            User user = qr.query("select * from user where id = ? limit 1", new BeanHandler<User>(User.class), id);
            if (user != null) {
                return user;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public User findByName(String name) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            User user = qr.query("select * from user where username = ? limit 1", new BeanHandler<>(User.class), name);
            if (user != null) {
                return user;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public User findByEmail(String email) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            User user = qr.query("select * from user where email = ? limit 1", new BeanHandler<>(User.class), email);
            if (user != null) {
                return user;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public UserInfo getUserInfo(Integer uid) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            UserInfo userInfo = qr.query("select * from user_info where user_id = ? ", new BeanHandler<>(UserInfo.class), uid);
            if (userInfo != null) {
                return userInfo;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public UserFocus findFocusById(Integer userId, Integer focusid) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            UserFocus userFocus = qr.query("select * from user_focus where user_id = ? and user_focus_id = ?", new BeanHandler<>(UserFocus.class), userId, focusid);
            if (userFocus != null) {
                return userFocus;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Integer addUser(User user) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            qr.update("insert into user value(?,?,?,?,?,?)",
                    user.getId(), user.getUsername(), user.getPassword(), user.getIsSecret(), user.getEmail(), user.getDeptId());
            User newUser = findByName(user.getUsername());
            Date now = new Date();
            UserInfo userInfo = new UserInfo();
            userInfo.setUserId(newUser.getId());
            userInfo.setLoginTime(now);
            userInfo.setRegisterTime(now);
            qr.update("insert into user_info value(?,?,?,?,?,?,?,?,?,?)",
                    userInfo.getUserId(), userInfo.getRealName(), userInfo.getAge(), userInfo.getPhone(), userInfo.getGender(),
                    userInfo.getDesc(), userInfo.getRegisterTime(), userInfo.getLoginTime(), userInfo.getPic(), userInfo.getLook());
            return newUser.getId();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Map<String, Object> getDetail(Integer userId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            String sql = "select ui.user_id as userId,ui.real_name as realName,ui.age,ui.gender,u.is_secret as isSecret," +
                    "ui.phone,ui.`desc` as introduce,ui.register_time as registerTime,ui.login_time as loginTime," +
                    "ui.pic,ui.look,u.dept_id as deptId,d.`name` as deptName," +
                    "(select count(*) as total from user_focus where user_focus_id = ui.user_id) as follows " +
                    "from user_info ui " +
                    "left join `user` u on u.id = ui.user_id " +
                    "left join dept d on d.id = u.dept_id " +
                    "where ui.user_id = ?";
            map = qr.query(sql, new MapHandler(), userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public Map<String, Object> findUserById(Integer userId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            String sql = "select u.id,u.username,u.dept_id as deptId,d.`name` as deptName,ui.pic " +
                    "from user u " +
                    "left join dept d on u.dept_id = d.id " +
                    "left join user_info ui on ui.user_id = u.id " +
                    "where u.id = ?";
            map = qr.query(sql, new MapHandler(), userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    //    public List<Map<String, Object>> findList(Integer userId,int page) {
//        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
//        List<Map<String, Object>> maps = new ArrayList<>();
//        try {
//            String sql = "select u.id as userId, ui.real_name as realName," +
//                    "ui.age,ui.gender,ui.`desc` as introduce,u.username,uf.id as ufId " +
//                    "from user_info ui " +
//                    "left join user u on ui.user_id = u.id " +
//                    "left join user_focus uf on uf.user_focus_id = u.id and uf.user_id = ? " +
//                    "order by u.id " +
//                    "limit ?,?";
//            maps = qr.query(sql, new MapListHandler(), userId,(page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return maps;
//    }
    public List<Map<String, Object>> findList(Integer userId, int page, String search) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id as userId, ui.real_name as realName,");
            sql.append("ui.age,ui.gender,ui.`desc` as introduce,u.username,uf.id as ufId ");
            sql.append("from user_info ui ");
            sql.append("left join user u on ui.user_id = u.id ");
            sql.append("left join user_focus uf on uf.user_focus_id = u.id and uf.user_id = ? ");
            if (null == search) {
                sql.append("order by u.id limit ?,? ");
                maps = qr.query(sql.toString(), new MapListHandler(), userId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            } else {
                sql.append("where ui.real_name like ? or u.username like ? ");
                sql.append("order by u.id limit ?,? ");
                search = search + "%";
                maps = qr.query(sql.toString(), new MapListHandler(), userId, search, search, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }

    public List<Map<String, Object>> getFocusUser(Integer userId, int page) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            String sql = "select uf.id, uf.user_id as userId, uf.user_focus_id as userFocusId, ui.real_name as realName, u.username as userName " +
                    "from user_focus uf " +
                    "left join user_info ui on ui.user_id = uf.user_focus_id " +
                    "left join user u on u.id = uf.user_focus_id " +
                    "where uf.user_id = ? " +
                    "order by u.id limit ?,? ";
            maps = qr.query(sql, new MapListHandler(), userId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }
}
